﻿using System;
using System.Linq;
using System.Text;

namespace Universe.MySqlTests
{
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    using System.Data.Entity.ModelConfiguration;
    using System.Data.Entity.ModelConfiguration.Conventions;
    using System.Data.SqlClient;
    using System.Diagnostics;

    using Dapper;
    using Dapper.Contrib;

    using global::Dapper;

    using JetBrains.Annotations;

    using LinqToDB;
    using LinqToDB.Common;
    using LinqToDB.DataProvider;
    using LinqToDB.DataProvider.MySql;
    using LinqToDB.DataProvider.SqlServer;

    using MySql.Data.MySqlClient;

    [Table("MyTable")]
    public class MyTable
    {
        [Key]
        public byte[] Id { get; set; }
        public String Para { get; set; }
        public byte[] Payload { get; set; }
        public DateTime Created { get; set; }
        public bool Enabled { get; set; }
        public decimal? Total;

        public bool? SampleBool { get; set; }
        public DateTime? SampleDateTime { get; set; }

        // LONGBLOB: 4G, MEDIUMBLOB: 16M, BLOB: 64K
        public static string SqlCreate = @"
Create Table MyTable
(
  Id binary(16) not null,
  /*Id char(16) binary not null,*/
  Para varchar(1000),
  Payload LONGBLOB,
  Created DateTime,
  Enabled bit,
  Total numeric(18,4),
  SampleBool bit,
  SampleDateTime DateTime,
  Constraint PK_MyTable Primary Key (Id)
);
";

    }

    public class MyTableMap : EntityTypeConfiguration<MyTable>
    {
        public void PhoneDataMap()
        {
            ToTable("MyTable");
            HasKey(m => m.Id);
            // Property(m => m.SomeProperty).HasColumnName("whatever").IsRequired();
            //etc.
        }
    }


    class MyContext : DbContext
    {
        
        public DbSet<MyTable> MyTableSet { get; set; }

        public MyContext()
            : base()
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        }

        public MyContext(DbConnection existingConnection, bool contextOwnsConnection)
            : base(existingConnection, contextOwnsConnection)
        {
        }

        public static MyContext Create()
        {
            MySqlConnection conn = new MySqlConnection("<connectionString>");
            conn.Open();

            var context = new MyContext(conn, false);
            return context;
        }

        public static void Insert(MySqlConnection con, IEnumerable<MyTable> list)
        {
            var sql = @"
Insert MyTable(Id, Para, Payload, Created, Enabled) Values(@Id, @Para, @Payload, @Created, @Enabled);
";
            con.Execute(sql, list);
        }

        public static void InsertSmarty(MySqlConnection con, IEnumerable<MyTable> list)
        {
            con.BatchInsert<MyTable>(list, commandTimeout: 60);
        }

        public static void ShowMyTableInfo(MySqlConnection con)
        {
            var tables = MySqlExtentions.GetTables(con, DbName);
            var t = tables.FirstOrDefault(x => "MyTable".Equals(x.Table, StringComparison.InvariantCultureIgnoreCase));
            if (t == null)
                Trace.WriteLine("Table " + "MyTable" + " is absent");
            else
            {
                Trace.WriteLine(string.Format("Table {0}: Data={1:n0}; Index={2:n0}; Free={3:n0}; Rows={4:n0}; Engine={5}; Collation={6}",
                    "MyTable", t.Data, t.Index, t.Free, t.Rows, t.Engine,
                    t.Collation));
            }
        }

        static IEnumerable<IEnumerable<T>> Partitions<T>(IEnumerable<T> list, int pageSize)
        {
            List<T> buffer = new List<T>(pageSize);
            foreach (var i in list)
            {
                buffer.Add(i);
                if (buffer.Count > pageSize)
                {
                    yield return buffer;
                    buffer.Clear();
                }
            }

            if (buffer.Count > 0)
                yield return buffer;
        }

        public static MySqlConnectionStringBuilder ConnectionBuilder
        {
            get
            {
                var b = MasterConnectionBuilder;
                b.Database = DbName;
                // its need for Mysql.Data.Entity only
                b.PersistSecurityInfo = true;
                return b;
            }
        }
        // TODO: Parse [protocol=Socket;port=3306;server=localhost;user id=root;password=pass]
        //       by MYSQL_CONNECTION 
        public static MySqlConnectionStringBuilder MasterConnectionBuilder
        {
            get
            {
                MySqlConnectionStringBuilder b = new MySqlConnectionStringBuilder()
                {
                    ConnectionProtocol = MySqlConnectionProtocol.Tcp,
                    Port = 3306,
                    Server = "localhost",
                    UserID = "root",
                    Password = "pass",
                    CharacterSet = "utf8",
                };

                // Trace.WriteLine("CS: [" + b.ConnectionString + "]");

                return b;
            }
        }

        public static readonly string DbName = "MySqlTests";

        public static void CheckSchema()
        {
            bool isDb, isTable;
            using (var con = MasterConnection)
            {
                const StringComparison c = StringComparison.InvariantCultureIgnoreCase;
                var dblist = MySqlExtentions.GetDatabases(con);
                isDb = dblist.Any(x => DbName.Equals(x.Name, c));
                var tables = MySqlExtentions.GetTables(con, DbName);
                isTable = tables.Any(x => "MyTable".Equals(x.Table, c));
                if (!isTable)
                {
                    if (!isDb)
                        con.Execute("create database " + DbName + ";");

                    var cs2 = ConnectionBuilder.ConnectionString;
                    using (var con2 = new MySqlConnection(cs2))
                    {
                        con2.Execute(MyTable.SqlCreate);
                    }
                }
            }

            using (var con = new MySqlConnection(ConnectionBuilder.ConnectionString))
            {
                var rows = con.Query("SHOW CREATE TABLE MyTable;");
                var row = rows.Single();
                IDictionary<string, object> d = (IDictionary<string, object>) row;

                var x = d["Create Table"];
                // string sql = row ;
                string info = "";
                if (!isDb) info += "Database " + DbName + " Created. ";
                if (!isTable) info += "Table MyTable Created. ";
                Trace.WriteLine(info + "The Structure is" + Environment.NewLine + x);
            }
        }

        public static MySqlConnection WorkingConnection
        {
            get
            {
                var b = ConnectionBuilder;
                var cs = b.ConnectionString;
                MySqlConnection con = new MySqlConnection(cs);
                con.Open();
                return con;
            }
        }
        
        public static MySqlConnection MasterConnection
        {
            get
            {
                var b = MasterConnectionBuilder;
                var cs = b.ConnectionString;
                MySqlConnection con = new MySqlConnection(cs);
                {
                    con.Open();
                    var ver = MySqlExtentions.GetVersionString(con);
                }

                return con;
            }
        }
    }

    internal class DbLinq : LinqToDB.Data.DataConnection
    {
        public DbLinq(IDbConnection connection) : base(GetDataProvider(), connection)
        {
            Configuration.AvoidSpecificDataProviderAPI = true;
        }

        private static IDataProvider GetDataProvider()
        {
            return new MySqlDataProvider() { };
        }


        public ITable<MyTable> TheTable { get { return GetTable<MyTable>(); } }

        // ... other tables ...
    }
}
